package com.stable.model;

import com.stable.spider.eastmoney.EastMoneySpider;
import com.stable.spider.ths.XLSData;
import com.stable.vo.BonusFinancing;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Font;

import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

//PE三阶段估值模型
public class PE3 {

    public static void PE3writeXls(String code, HSSFWorkbook workbook, List<XLSData> benefitList, List<XLSData> cashList, List<XLSData> debtList, List<XLSData> mainList) {
        HSSFSheet sheet = workbook.createSheet("股东价值-PE三阶段估值模型");// 创建工作表(Sheet)
        List nullList = Arrays.asList();

        HSSFUtil.row(workbook, sheet, 0, code + ":报告参数", "科目\\时间", benefitList);
        HSSFUtil.row(workbook, sheet, 1, "净利润:NI", "净利润(元)", mainList);

        HSSFUtil.rowFormula(workbook, sheet, 2, "净利润：亿", "CS2/100000000", benefitList, "0.00");

        EastMoneySpider eastMoneySpider = new EastMoneySpider();
        Map<String, BonusFinancing> bfMap = eastMoneySpider.getBonusFinancing(code).stream().collect(Collectors.toMap(BonusFinancing::getSj, bf -> bf, (oldBf, newBf) -> newBf));
        benefitList.stream().filter(xlsData -> StringUtils.equals(xlsData.getKey(), "科目\\时间")).forEach(xlsData -> {
            HSSFRow row4 = sheet.createRow(4);
            HSSFUtil.rowColumn(workbook, row4, 0, "分红明细", Font.COLOR_NORMAL);
            if (0 != xlsData.getYData1()) {
                HSSFUtil.rowColumn(workbook, row4, 1, bfMap.get(new Double(xlsData.getYData1()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData1()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData2()) {
                HSSFUtil.rowColumn(workbook, row4, 2, bfMap.get(new Double(xlsData.getYData2()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData2()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData3()) {
                HSSFUtil.rowColumn(workbook, row4, 3, bfMap.get(new Double(xlsData.getYData3()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData3()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData4()) {
                HSSFUtil.rowColumn(workbook, row4, 4, bfMap.get(new Double(xlsData.getYData4()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData4()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData5()) {
                HSSFUtil.rowColumn(workbook, row4, 5, bfMap.get(new Double(xlsData.getYData5()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData5()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
            if (0 != xlsData.getYData6()) {
                HSSFUtil.rowColumn(workbook, row4, 6, bfMap.get(new Double(xlsData.getYData6()).intValue() +"") == null ? "0" : bfMap.get(new Double(xlsData.getYData6()).intValue() +"").getMgsy(), Font.COLOR_NORMAL);
            }
        });

        HSSFRow row5 = sheet.createRow(5);
        HSSFUtil.rowColumn(workbook, row5, 0, "分红：亿", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row5, 1, "B5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 2, "C5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 3, "D5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 4, "E5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 5, "F5/10000", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row5, 6, "G5/10000", Font.COLOR_NORMAL, "0.00");


        HSSFRow row6 = sheet.createRow(6);
        HSSFUtil.rowColumn(workbook, row6, 0, "分红率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row6, 1, "B6/B3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 2, "C6/C3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 3, "D6/D3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 4, "E6/E3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 5, "F6/F3", Font.COLOR_NORMAL, "0.00%");
        HSSFUtil.rowColumnFormula(workbook, row6, 6, "G6/G3", Font.COLOR_NORMAL, "0.00%");

        HSSFUtil.row(workbook, sheet, 7, "加权净资产收益率", "净资产收益率", mainList);
        HSSFUtil.row(workbook, sheet, 8, "净利润同比增长率", "净利润同比增长率", mainList);
        HSSFUtil.row(workbook, sheet, 9, "扣非净利润同比增长率", "扣非净利润同比增长率", mainList);


        HSSFRow row11 = sheet.createRow(11);
        HSSFUtil.rowColumn(workbook, row11, 1, "a值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row11, 2, "b值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row11, 3, "2019年模型值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row11, 4, "年化增速", Font.COLOR_NORMAL);

        HSSFRow row12 = sheet.createRow(12);
        HSSFUtil.rowColumn(workbook, row12, 0, "用指数函数回归：过去五年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row12, 1, "INDEX(LOGEST(B3:K3,B$1:K$1),1)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row12, 2, "INDEX(LOGEST(B3:K3,B$1:K$1),2)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row12, 3, "C13*B13^B1", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row12, 4, "B13-1", Font.COLOR_NORMAL, "0.00%");

        HSSFRow row13 = sheet.createRow(13);
        HSSFUtil.rowColumn(workbook, row13, 0, "用指数函数回归：过去十年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row13, 1, "INDEX(LOGEST(B3:F3,B$1:F$1),1)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row13, 2, "INDEX(LOGEST(B3:F3,B$1:F$1),2)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row13, 3, "C14*B14^B1", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row13, 4, "B14-1", Font.COLOR_NORMAL, "0.00%");



        HSSFRow row15 = sheet.createRow(15);
        HSSFUtil.rowColumn(workbook, row15, 0, "1、股东价值-PE三阶段估值模型（过去5年）", Font.COLOR_NORMAL);

        HSSFRow row16 = sheet.createRow(16);
        HSSFUtil.rowColumn(workbook, row16, 0, "三阶段模型", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 1, "利润增长率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 2, "利润", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 3, "分红率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 4, "ROE", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 5, "股东价值率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 6, "折现率", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 7, "折现系数", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 8, "股东价值折现", Font.COLOR_NORMAL);
        HSSFUtil.rowColumn(workbook, row16, 9, "股东价值折现求和", Font.COLOR_NORMAL);

        HSSFRow row17 = sheet.createRow(17);
        HSSFUtil.rowColumn(workbook, row17, 0, "未来10年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row17, 1, "AVERAGE(B9:F9)/100", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row17, 3, "AVERAGE(C7:G7)", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row17, 4, "AVERAGE(B8:F8)/100", Font.COLOR_RED, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row17, 5, "D18+(1-D18)*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumn(workbook, row17, 6, "10%", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row17, 9, "1-10年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row17, 10, "(1+B18)*F18/(G18-B18)-((1+B18)^11)*F18/(G18-B18)/(1+G18)^10", Font.COLOR_RED, "0.0");

        HSSFRow row18 = sheet.createRow(18);
        HSSFUtil.rowColumn(workbook, row18, 0, "11年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row18, 1, "B18-(B18-B29)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row18, 2, "(1+B18)^10*(1+B19)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row18, 3, "D18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row18, 4, "B19/B18*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row18, 5, "D19+(1-D19)*E19", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row18, 6, "G18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row18, 7, "1/(1+G19)^11", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row18, 8, "C19*F19*H19", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row18, 9, "10-20年", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row18, 10, "SUM(I19:I28)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row19 = sheet.createRow(19);
        HSSFUtil.rowColumn(workbook, row19, 0, "12年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row19, 1, "B19-(B18-B29)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row19, 2, "C19*(1+B20)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row19, 3, "D18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row19, 4, "B20/B18*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row19, 5, "D20+(1-D20)*E20", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row19, 6, "G18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row19, 7, "H19/(1+G20)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row19, 8, "C20*F20*H20", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row19, 9, "20年后", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row19, 10, "C28*(1+B29)*F29/(G29-B29)*H28", Font.COLOR_NORMAL, "0.00");


        HSSFRow row20 = sheet.createRow(20);
        HSSFUtil.rowColumn(workbook, row20, 0, "13年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row20, 1, "B20-(B18-B29)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 2, "C20*(1+B21)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row20, 3, "D18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 4, "B21/B18*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 5, "D21+(1-D21)*E21", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 6, "G18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row20, 7, "H20/(1+G21)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row20, 8, "C21*F21*H21", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row20, 9, "三部分相加", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row20, 10, "SUM(K18:K20)", Font.COLOR_NORMAL, "0.00");

        HSSFRow row21 = sheet.createRow(21);
        HSSFUtil.rowColumn(workbook, row21, 0, "14年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row21, 1, "B21-(B18-B29)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row21, 2, "C21*(1+B22)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row21, 3, "D18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row21, 4, "B22/B18*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row21, 5, "D22+(1-D22)*E22", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row21, 6, "G18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row21, 7, "H21/(1+G22)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row21, 8, "C22*F22*H22", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row21, 9, "过去五年模型值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row21, 10, "D13", Font.COLOR_NORMAL, "0.00");

        HSSFRow row22 = sheet.createRow(22);
        HSSFUtil.rowColumn(workbook, row22, 0, "15年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row22, 1, "B22-(B18-B29)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row22, 2, "C22*(1+B23)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row22, 3, "D18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row22, 4, "B23/B18*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row22, 5, "D23+(1-D23)*E23", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row22, 6, "G18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row22, 7, "H22/(1+G23)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row22, 8, "C23*F23*H23", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumn(workbook, row22, 9, "合理市值", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row22, 10, "K21*K22", Font.COLOR_NORMAL, "0.00");


        HSSFRow row23 = sheet.createRow(23);
        HSSFUtil.rowColumn(workbook, row23, 0, "16年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row23, 1, "B23-(B18-B29)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 2, "C23*(1+B24)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row23, 3, "D18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 4, "B24/B18*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 5, "D24+(1-D24)*E24", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 6, "G18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row23, 7, "H23/(1+G24)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row23, 8, "C24*F24*H24", Font.COLOR_NORMAL, "0.00");

        HSSFUtil.rowColumn(workbook, row23, 9, "总股本：亿", Font.COLOR_NORMAL);
        Double zgb = eastMoneySpider.getCapitalStockStructure(code);
        HSSFUtil.rowColumn(workbook, row23, 10, zgb +"", Font.COLOR_NORMAL);

        HSSFRow row24 = sheet.createRow(24);
        HSSFUtil.rowColumn(workbook, row24, 0, "17年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row24, 1, "B24-(B18-B29)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 2, "C24*(1+B25)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row24, 3, "D18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 4, "B25/B18*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 5, "D25+(1-D25)*E25", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 6, "G18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row24, 7, "H24/(1+G25)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row24, 8, "C25*F25*H25", Font.COLOR_NORMAL, "0.00");


        HSSFRow row25 = sheet.createRow(25);
        HSSFUtil.rowColumn(workbook, row25, 0, "18年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row25, 1, "B25-(B18-B29)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 2, "C25*(1+B26)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row25, 3, "D18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 4, "B26/B18*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 5, "D26+(1-D26)*E26", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 6, "G18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row25, 7, "H25/(1+G26)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row25, 8, "C26*F26*H26", Font.COLOR_NORMAL, "0.00");

        HSSFRow row26 = sheet.createRow(26);
        HSSFUtil.rowColumn(workbook, row26, 0, "19年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row26, 1, "B26-(B18-B29)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 2, "C26*(1+B27)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row26, 3, "D18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 4, "B27/B18*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 5, "D27+(1-D27)*E27", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 6, "G18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row26, 7, "H26/(1+G27)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row26, 8, "C27*F27*H27", Font.COLOR_NORMAL, "0.00");

        HSSFRow row27 = sheet.createRow(27);
        HSSFUtil.rowColumn(workbook, row27, 0, "20年", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row27, 1, "B27-(B18-B29)/10", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 2, "C27*(1+B28)", Font.COLOR_NORMAL, "0.0");
        HSSFUtil.rowColumnFormula(workbook, row27, 3, "D18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 4, "B28/B18*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 5, "D28+(1-D28)*E28", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 6, "G18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row27, 7, "H27/(1+G28)", Font.COLOR_NORMAL, "0.00");
        HSSFUtil.rowColumnFormula(workbook, row27, 8, "C28*F28*H28", Font.COLOR_NORMAL, "0.00");


        HSSFRow row28 = sheet.createRow(28);
        HSSFUtil.rowColumn(workbook, row28, 0, "20年后", Font.COLOR_RED);
        HSSFUtil.rowColumn(workbook, row28, 1, "2%", Font.COLOR_RED);
        HSSFUtil.rowColumnFormula(workbook, row28, 3, "D18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 4, "B29/B18*E18", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 5, "D29+(1-D29)*E29", Font.COLOR_NORMAL, "0.0%");
        HSSFUtil.rowColumnFormula(workbook, row28, 6, "G18", Font.COLOR_NORMAL, "0.0%");


        HSSFUtil.rowColumn(workbook, row28, 9, "合理股价", Font.COLOR_NORMAL);
        HSSFUtil.rowColumnFormula(workbook, row28, 10, "K23/K24", Font.COLOR_NORMAL, "0.00");
    }
}
